﻿//using System;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

//using System.Drawing;
//using zkweb;

namespace YG.CommonFuncs
{
    /// <summary>
    /// Excel导出帮助类
    /// </summary>
    public static class Data2XLS
    {
        #region npoi

        public static string DataTableToXLS(DataTable dt, string strFileName)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet;

            int headRowIndex = 0;
            string sheetName = "Sheet1";
            if (!string.IsNullOrEmpty(dt.TableName))
            {
                sheetName = dt.TableName;
            }
            sheet = workbook.CreateSheet(sheetName);
            int rowIndex = 0;

            #region 列头及样式

            {
                XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);

                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.Center;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);

                foreach (DataColumn column in dt.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                }
            }

            #endregion 列头及样式

            #region 填充内容

            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    string drValue = row[column].ToString();
                    dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
                }
            }

            #endregion 填充内容

            MemoryStream ms = new MemoryStream();
            //workbook.Write
            workbook.Write(ms);
            var buf = ms.ToArray();
            //保存为Excel文件
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }

            //ms.Flush();

            return strFileName;
        }

        #endregion npoi

        #region npoi

        public static string DataSetToXLS(DataSet ds, string strFileName, int insamesheet)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();

            if (insamesheet == 1)
            {
                ISheet sheet;

                int headRowIndex = 0;
                int rowIndex = 0;
                string sheetName = "Sheet1";
                if (!string.IsNullOrEmpty(ds.Tables[0].TableName))
                {
                    sheetName = ds.Tables[0].TableName;
                }
                sheet = workbook.CreateSheet(sheetName);

                foreach (DataTable dT in ds.Tables)
                {
                    #region 列头及样式

                    XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);

                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);

                    foreach (DataColumn column in dT.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                    }

                    #endregion 列头及样式

                    #region 填充内容

                    foreach (DataRow row in dT.Rows)
                    {
                        rowIndex++;
                        XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                        foreach (DataColumn column in dT.Columns)
                        {
                            string drValue = row[column].ToString();
                            dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
                        }
                    }

                    #endregion 填充内容

                    headRowIndex += dT.Rows.Count + 3;
                    rowIndex += 3;
                }

                MemoryStream ms = new MemoryStream();
                //workbook.Write
                workbook.Write(ms);
                var buf = ms.ToArray();
                //保存为Excel文件
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }

                //ms.Flush();

                return strFileName;
            }
            else
            {
                foreach (DataTable dT in ds.Tables)
                {
                    ISheet sheet;

                    int headRowIndex = 0;
                    string sheetName = "Sheet1";
                    if (!string.IsNullOrEmpty(dT.TableName))
                    {
                        sheetName = dT.TableName;
                    }
                    sheet = workbook.CreateSheet(sheetName);
                    int rowIndex = 0;

                    #region 列头及样式

                    XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);

                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);

                    foreach (DataColumn column in dT.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                    }

                    #endregion 列头及样式

                    #region 填充内容

                    foreach (DataRow row in dT.Rows)
                    {
                        rowIndex++;
                        XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                        foreach (DataColumn column in dT.Columns)
                        {
                            string drValue = row[column].ToString();
                            dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
                        }
                    }

                    #endregion 填充内容
                }

                MemoryStream ms = new MemoryStream();
                //workbook.Write
                workbook.Write(ms);
                var buf = ms.ToArray();
                //保存为Excel文件
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }

                //ms.Flush();

                return strFileName;
            }
        }

        #endregion npoi
    }
}